package pl.edu.pw.elka.spdb;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

public class SpdbDAOImpl implements SpdbDAO {

    private static final String INSERT_POINTS_STANDARD = "insert into POINTS_STANDARD values(?, ?, ?, ?)";
    private static final String INSERT_POINTS_SPATIAL = "insert into POINTS_SPATIAL values(?, ?, SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL))";
    private static final String INSERT_RECTANGLES_SPATIAL = "insert into RECTANGLES_SPATIAL values(?, ?, SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)))";
    private static final String INSERT_RECTANGLES_STANDARD = "insert into RECTANGLES_STANDARD values(?, ?, ?, ?, ?, ?)";
    //dlaczego MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,3) ? w dok pisze ze 3 oznacza ze nie wiesz czy obiekt jest zewnetrzny czy wewnetrzny i nie powinno sie uzywac tej wartosci
    private static final String INSERT_GROUPED_RECTANGLES_SPATIAL = "insert into RECTANGLES_SPATIAL_GROUP values(?, ?, SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)))";
    private static final String INSERT_GROUPED_RECTANGLES_STANDARD = "insert into RECTANGLES_STANDARD_GROUP values(?, ?, ?, ?, ?, ?)";
    private static final String INSERT_PENTAGONS_SPATIAL = "insert into PENTAGONS_SPATIAL values(?, ?, SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY (1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?, ?,?,?,?, ?,?)))";
    private static final String INSERT_PENTAGONS_STANDARD = "insert into PENTAGONS_STANDARD values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String INSERT_POINTS_STANDARD_GROUP = "insert into POINTS_STANDARD_GROUP values(?, ?, ?, ?)";
    private static final String INSERT_POINTS_SPATIAL_GROUP = "insert into POINTS_SPATIAL_GROUP values(?, ?, SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL))";
    private static final String INSERT_PATH_STANDARD = "insert into PATHS_STANDARD values(?, ?, ?, ?, ?)";
    private static final String[] INSERT_PATH_SPATIAL = {"insert into PATHS_SPATIAL values(", ", SDO_GEOMETRY(2002,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1), MDSYS.SDO_ORDINATE_ARRAY (", ") ), ", ")"};
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void insertPointsStandard(final List<Point> points) {
        try {
            System.out.println("inserting points - standard");

            jdbcTemplate.batchUpdate(INSERT_POINTS_STANDARD,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Point point = points.get(i);
                            ps.setInt(1, point.getId());
                            ps.setString(2, point.getNazwa());
                            ps.setDouble(3, point.getX());
                            ps.setDouble(4, point.getY());
                        }

                        @Override
                        public int getBatchSize() {
                            return points.size();
                        }
                    });
            System.out.println("Insert finished for " + points.size() + " elements.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertPointsSpatial(final List<Point> points) {
        try {
            System.out.println("inserting points - spatial");

            jdbcTemplate.batchUpdate(INSERT_POINTS_SPATIAL,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Point point = points.get(i);
                            ps.setInt(1, point.getId());
                            ps.setString(2, point.getNazwa());
                            ps.setDouble(3, point.getX());
                            ps.setDouble(4, point.getY());
                        }

                        @Override
                        public int getBatchSize() {
                            return points.size();
                        }
                    });
            System.out.println("Insert finished for " + points.size() + " elements.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertPointsStandardGroup(final List<Point> points) {
        try {
            System.out.println("inserting points - standard");

            jdbcTemplate.batchUpdate(INSERT_POINTS_STANDARD_GROUP,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Point point = points.get(i);
                            ps.setInt(1, point.getId());
                            ps.setString(2, point.getNazwa());
                            ps.setDouble(3, point.getX());
                            ps.setDouble(4, point.getY());
                        }

                        @Override
                        public int getBatchSize() {
                            return points.size();
                        }
                    });
            System.out.println("Insert finished for " + points.size() + " elements.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertPointsSpatialGroup(final List<Point> points) {
        try {
            System.out.println("inserting points - spatial");

            jdbcTemplate.batchUpdate(INSERT_POINTS_SPATIAL_GROUP,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Point point = points.get(i);
                            ps.setInt(1, point.getId());
                            ps.setString(2, point.getNazwa());
                            ps.setDouble(3, point.getX());
                            ps.setDouble(4, point.getY());
                        }

                        @Override
                        public int getBatchSize() {
                            return points.size();
                        }
                    });
            System.out.println("Insert finished for " + points.size() + " elements.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertPathsSpatial(final List<Path> paths) {
        try {

            for (int i = 0; i < paths.size(); i++) {
                if (i % 100 == 99) {
                    System.out.println("sleeping...");
                    Thread.sleep(1000);

                }

                Path p = paths.get(i);
                String args = new String();
                for (int j = 0; j < p.getPoints().size(); j++) {
                    Point pp = p.getPoints().get(j);
                    if (j > 0) {
                        args += ", ";
                    }
                    args += pp.getX() + ", " + pp.getY();
                }
                jdbcTemplate.update(INSERT_PATH_SPATIAL[0] + p.getId() + INSERT_PATH_SPATIAL[1] + args + INSERT_PATH_SPATIAL[2] + "'" + p.getNazwa() + "'" + INSERT_PATH_SPATIAL[3]);
            }
            System.out.println("inserting paths - spatial");

            System.out.println("Insert finished for " + paths.size() + " elements.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertPathsStandard(final List<Path> paths) {
        try {
            System.out.println("inserting paths - standard");
            int i = 0;
            for (Path path : paths) {
                if (i % 100 == 99) {
                    System.out.println("sleeping...");
                    Thread.sleep(1000);
                }

                i++;
                final Path p = path;
                jdbcTemplate.batchUpdate(INSERT_PATH_STANDARD,
                        new BatchPreparedStatementSetter() {

                            @Override
                            public void setValues(PreparedStatement ps, int i)
                                    throws SQLException {
                                final Point point = p.getPoints().get(i);
                                ps.setInt(1, point.getId());
                                ps.setInt(2, p.getId());
                                ps.setDouble(3, point.getX());
                                ps.setDouble(4, point.getY());
                                ps.setString(5, p.getNazwa());
                            }

                            @Override
                            public int getBatchSize() {
                                return p.getPoints().size();
                            }
                        });
            }
            System.out.println("Insert finished for " + paths.size() + " elements.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }
    }

    @Override
    public void insertRectanglesStandard(final List<Rectangle> rectangles) {
        try {
            System.out.println("inserting rectangles - standard");

            jdbcTemplate.batchUpdate(INSERT_RECTANGLES_STANDARD,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Rectangle rectangle = rectangles.get(i);
                            ps.setInt(1, rectangle.getId());
                            ps.setString(2, rectangle.getNazwa());
                            ps.setDouble(3, rectangle.getX());
                            ps.setDouble(4, rectangle.getY());
                            ps.setDouble(5, rectangle.getWidth());
                            ps.setDouble(6, rectangle.getHeight());
                        }

                        @Override
                        public int getBatchSize() {
                            return rectangles.size();
                        }
                    });
            System.out.println("Insert finished for " + rectangles.size() + " rectangles.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertRectanglesSpatial(final List<Rectangle> rectangles) {
        try {
            System.out.println("inserting rectangles - spatial");

            jdbcTemplate.batchUpdate(INSERT_RECTANGLES_SPATIAL,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Rectangle rectangle = rectangles.get(i);
                            ps.setInt(1, rectangle.getId());
                            ps.setString(2, rectangle.getNazwa());
                            ps.setDouble(3, rectangle.getX());
                            ps.setDouble(4, rectangle.getY());
                            ps.setDouble(5, rectangle.getX() + rectangle.getWidth());
                            ps.setDouble(6, rectangle.getY() + rectangle.getHeight());
                        }

                        @Override
                        public int getBatchSize() {
                            return rectangles.size();
                        }
                    });
            System.out.println("Insert finished for " + rectangles.size() + " rectangles.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertGroupedRectanglesStandard(final List<Rectangle> rectangles) {
        try {
            System.out.println("inserting grouped rectangles - standard");

            jdbcTemplate.batchUpdate(INSERT_GROUPED_RECTANGLES_STANDARD,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Rectangle rectangle = rectangles.get(i);
                            ps.setInt(1, rectangle.getId());
                            ps.setString(2, rectangle.getNazwa());
                            ps.setDouble(3, rectangle.getX());
                            ps.setDouble(4, rectangle.getY());
                            ps.setDouble(5, rectangle.getWidth());
                            ps.setDouble(6, rectangle.getHeight());
                        }

                        @Override
                        public int getBatchSize() {
                            return rectangles.size();
                        }
                    });
            System.out.println("Insert finished for " + rectangles.size() + " rectangles.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertGroupedRectanglesSpatial(final List<Rectangle> rectangles) {
        try {
            System.out.println("inserting grouped rectangles - spatial");

            jdbcTemplate.batchUpdate(INSERT_GROUPED_RECTANGLES_SPATIAL,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Rectangle rectangle = rectangles.get(i);
                            ps.setInt(1, rectangle.getId());
                            ps.setString(2, rectangle.getNazwa());
                            ps.setDouble(3, rectangle.getX());
                            ps.setDouble(4, rectangle.getY());
                            ps.setDouble(5, rectangle.getX() + rectangle.getWidth());
                            ps.setDouble(6, rectangle.getY() + rectangle.getHeight());
                        }

                        @Override
                        public int getBatchSize() {
                            return rectangles.size();
                        }
                    });
            System.out.println("Insert finished for " + rectangles.size() + " rectangles.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }

    }

    @Override
    public void insertPentagonsStandard(final List<Pentagon> pentagons) {
        try {
            System.out.println("inserting pentagons - standard");

            jdbcTemplate.batchUpdate(INSERT_PENTAGONS_STANDARD,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Pentagon pentagon = pentagons.get(i);
                            ps.setInt(1, pentagon.getId());
                            ps.setString(2, pentagon.getNazwa());
                            ps.setDouble(3, pentagon.getP1().getX());
                            ps.setDouble(4, pentagon.getP1().getY());
                            ps.setDouble(5, pentagon.getP2().getX());
                            ps.setDouble(6, pentagon.getP2().getY());
                            ps.setDouble(7, pentagon.getP3().getX());
                            ps.setDouble(8, pentagon.getP3().getY());
                            ps.setDouble(9, pentagon.getP4().getX());
                            ps.setDouble(10, pentagon.getP4().getY());
                            ps.setDouble(11, pentagon.getP5().getX());
                            ps.setDouble(12, pentagon.getP5().getY());
                        }

                        @Override
                        public int getBatchSize() {
                            return pentagons.size();
                        }
                    });
            System.out.println("Insert finished for " + pentagons.size() + " pentagons.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }
    }

    @Override
    public void insertPentagonsSpatial(final List<Pentagon> pentagons) {
        try {
            System.out.println("inserting pentagons - spatial");

            jdbcTemplate.batchUpdate(INSERT_PENTAGONS_SPATIAL,
                    new BatchPreparedStatementSetter() {

                        @Override
                        public void setValues(PreparedStatement ps, int i)
                                throws SQLException {
                            final Pentagon pentagon = pentagons.get(i);
                            ps.setInt(1, pentagon.getId());
                            ps.setString(2, pentagon.getNazwa());
                            ps.setDouble(3, pentagon.getP1().getX());
                            ps.setDouble(4, pentagon.getP1().getY());
                            ps.setDouble(5, pentagon.getP2().getX());
                            ps.setDouble(6, pentagon.getP2().getY());
                            ps.setDouble(7, pentagon.getP3().getX());
                            ps.setDouble(8, pentagon.getP3().getY());
                            ps.setDouble(9, pentagon.getP4().getX());
                            ps.setDouble(10, pentagon.getP4().getY());
                            ps.setDouble(11, pentagon.getP5().getX());
                            ps.setDouble(12, pentagon.getP5().getY());
                        }

                        @Override
                        public int getBatchSize() {
                            return pentagons.size();
                        }
                    });
            System.out.println("Insert finished for " + pentagons.size() + " pentagons.");
        } catch (Exception ex) {
            System.err.println("Error when performing an insert...");
            ex.printStackTrace();
        }
    }
}
